package sax;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.AbstractList;

public class MySqlWorker {
	/**to execute queries and updates.*/
	private Statement statement;
	/** connection to database. */
	private Connection connection;
	private StringBuffer strbf = new StringBuffer();
	private PreparedStatement addPersonSt;
	private PreparedStatement addTripSt;
	private PreparedStatement carshopSt;

	/**
	 * this constructor initialize many parameters and creates all prepared
	 * statements so later they can be used without concern. it is used string
	 * buffer to store the command for creating the statements.
	 * 
	 * @param password
	 *            is password for given DB
	 * @param url
	 *            is address of some DB
	 * @param userName
	 *            is user name for given DB
	 * @throws SQLException
	 *             if some error occur during communication with DB
	 */

	public MySqlWorker(String url, String user, String passwd)
			throws SQLException {

		connection = DriverManager.getConnection(url, user, passwd);
		statement = connection.createStatement();

		statement.executeUpdate("DROP table IF EXISTS PeopleNew");
		statement.executeUpdate("DROP table IF EXISTS TripNew");
		statement.executeUpdate("DROP table IF EXISTS carshopNEW");

		eraseStrbf()
				.append(
						"CREATE TABLE IF NOT EXISTS PeopleNEW (id  TINYINT AUTO_INCREMENT NOT NULL PRIMARY KEY ,"
								+ "name VARCHAR(20), pid BIGINT UNSIGNED NOT NULL UNIQUE,"
								+ " age TINYINT UNSIGNED, email VARCHAR(25))");
		statement.executeUpdate(strbf.toString());
		eraseStrbf().append(
				"INSERT INTO PeopleNew (name,pid,age,email) VALUES (?,?,?,?)");
		addPersonSt = connection.prepareStatement(strbf.toString());

		eraseStrbf()
				.append(
						"CREATE TABLE IF NOT EXISTS TripNEW (id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,"
								+ " city VARCHAR(20), ticknumb INT UNSIGNED NOT NULL UNIQUE, "
								+ "arrivdate DATE NOT NULL DEFAULT '2008-01-01'  , "
								+ "departdate DATE NOT NULL DEFAULT '2008-01-01' )");
		statement.executeUpdate(strbf.toString());
		eraseStrbf()
				.append(
						"INSERT INTO TripNEW (city,ticknumb,arrivdate,departdate) VALUES (?,?,?,?)");
		addTripSt = connection.prepareStatement(strbf.toString());

		eraseStrbf()
				.append(
						"CREATE TABLE IF NOT EXISTS carshopNEW (id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,"
								+ "model VARCHAR(35) NOT NULL, "
								+ "count TINYINT UNSIGNED NOT NULL DEFAULT 0)");
		statement.executeUpdate(strbf.toString());
		eraseStrbf()
				.append("INSERT INTO carshopNEW (model,count) VALUES (?,?)");
		carshopSt = connection.prepareStatement(strbf.toString());

	}

	public int addToPeople(AbstractList<String> values) throws SQLException {
		addPersonSt.setString(1, values.get(0));
		addPersonSt.setLong(2, Long.valueOf(values.get(1)));
		addPersonSt.setByte(3, Byte.valueOf(values.get(2)));
		addPersonSt.setString(4, values.get(3));
		return addPersonSt.executeUpdate();
	}

	public int addToTrips(AbstractList<String> values) throws SQLException {
		addTripSt.setString(1, values.get(0));
		addTripSt.setInt(2, Integer.valueOf(values.get(1)));
		addTripSt.setDate(3, Date.valueOf(values.get(2)));
		addTripSt.setDate(4, Date.valueOf(values.get(3)));
		return addTripSt.executeUpdate();
	}

	public int addToCarshop(AbstractList<String> values) throws SQLException {
		carshopSt.setString(1, values.get(0));
		carshopSt.setByte(2, Byte.valueOf(values.get(1)));
		return carshopSt.executeUpdate();
	}

	public ResultSet returnAllFromTable(String table) throws SQLException {

		return statement.executeQuery("SELECT * FROM " + table);
	}

	private StringBuffer eraseStrbf() {
		return strbf.delete(0, strbf.length());
	}

	public void close() throws SQLException {
		connection.close();

	}

}
